package com.health.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import com.health.model.LvYueFuWuBean;
import com.health.tablebean.JTYSCustomerDoctorCont;
import com.health.viewscontrols.NovaApplication;
import com.health.tablebean.ARCHIVE_BASEINFO;
import com.health.tablebean.HD_PERSONAL_HEALTH_PHOTO;
import com.health.tools.GlobalConts;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 * 数据库工具类
 *
 * @author 田相伟
 */
public class DbManager {

    private static final String TAG = "DbManager";

    public SQLiteDatabase db;
    public static final int pagesize = 9;
    public static final String TYPE_SIGN = "type_sign";
    public static final String TYPE_UNSIGN = "type_unsign";
    public static final String TYPE_ALL = "type_all";

    static Context ctx;

    private static class StaticHolder {
        private static final DbManager INSTANCE = new DbManager(ctx.getApplicationContext());

    }

    public static final DbManager getInstance(Context context) {
        ctx = context;
        return StaticHolder.INSTANCE;
    }

    public DbManager(Context context) {
        SQLdm s = new SQLdm();
        db = s.openDatabase(context);
    }

    // public List<BD_DD_INFO> fenYeFind(int page) {
    // Cursor cursor = db.rawQuery("select * from
    // "+SQLdm.getARCHIVE_ASSESSMENTGUIDE_TableName()+" order by ID limit ? ",
    // new String[]{page*10+""});
    // String name = null;
    // System.out.println("8888888888888888888888");
    // while (cursor.moveToNext()) {
    // name = cursor.getString(cursor.getColumnIndex("IDCARD"));
    // System.out.println(name+"**********************");
    // }
    // cursor.close();
    // return null;
    // }

    // /**
    // * 居民健康档案
    // */
    // //保存居民健康档案
    // public void saveARCHIVE_CARD(ARCHIVE_CARD aRCHIVE_CARD){
    // Object[] arrayOfObject = new Object[11];
    // db.execSQL(, bindArgs);
    // }

    /**
     * 判断照片表是否存在
     */
    public boolean isExistPhoto() {
        boolean flag = false;
        try {
            db.execSQL(
                    " select count(*) from sqlite_master where type = 'table' and name = 'HD_PERSONAL_HEALTH_PHOTO'");
            flag = true;
            Log.w("判断照片表是否存在-----", "存在");
        } catch (Exception e) {
            e.printStackTrace();
            flag = false;
            Log.w("判断照片表是否存在-----", "不存在");
        }
        return flag;

    }

    //判断履约表是否存在
//    public boolean isExistLvyue() {
//        boolean flag = false;
//        try {
//            db.execSQL(
//                    " select count(*) from sqlite_master where type = 'table' and name = 'LvYueFuWuBean'");
//            flag = true;
//            Log.w("判断照片表是否存在-----", "存在");
//        } catch (Exception e) {
//            e.printStackTrace();
//            flag = false;
//            Log.w("判断照片表是否存在-----", "不存在");
//        }
//        return flag;
//
//    }
    public boolean isExistLvyue() {
        boolean result = false;

        Cursor cursor = null;
        try {

            String sql = "select count(*) as c from sqlite_master where type ='table' and name ='LvYueFuWuBean'";
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
        return result;
    }

    public void addNewTable() {
        //冠心病
        db.execSQL("create table if not exists gxb "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),sex VARCHAR(200),"
                + "happentime VARCHAR(200),visityType VARCHAR(200),sfbr VARCHAR(200),tgrgx VARCHAR(200),"
                + "height VARCHAR(200),weight VARCHAR(200),yaowei VARCHAR(200),ywfb VARCHAR(200),fbcs VARCHAR(200),"
                + "zd VARCHAR(200),zdjg VARCHAR(200),gy VARCHAR(200),dy VARCHAR(200),kzqk VARCHAR(200),sfxf VARCHAR(200),qztime VARCHAR(200),sffy VARCHAR(200),yyqk VARCHAR(200),sfys VARCHAR(200))");

        //关节炎
        db.execSQL("create table if not exists gjy "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),"
                + "sex VARCHAR(200),happentime VARCHAR(200),visityType VARCHAR(200),sfbr VARCHAR(200),"
                + "tgrgx VARCHAR(200),zz VARCHAR(200),zzqt VARCHAR(200),gjzz VARCHAR(200),gjjx VARCHAR(200),"
                + "gjyt VARCHAR(200),mcg VARCHAR(200),weight VARCHAR(200),tzqt VARCHAR(200),kztz VARCHAR(200),xltz VARCHAR(200),xz VARCHAR(200),qc VARCHAR(200),yy VARCHAR(200),fyycx VARCHAR(200),ywblfy VARCHAR(200),sffl VARCHAR(200),ywmc1 VARCHAR(200),mr1 VARCHAR(200),mc1 VARCHAR(200),ywmc2 VARCHAR(200),mr2 VARCHAR(200),mc2 VARCHAR(200),ywmc3 VARCHAR(200),mr3 VARCHAR(200),mc3 VARCHAR(200),sfys VARCHAR(200))");

        //类风湿关节炎
        db.execSQL("create table if not exists lfsgjy "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),"
                + "sex VARCHAR(200),happentime VARCHAR(200),sysy VARCHAR(200),visityType VARCHAR(200),sfbr VARCHAR(200),"
                + "tgrgx VARCHAR(200),zz VARCHAR(200),zzqt VARCHAR(200),gjjx VARCHAR(200),lfsjj VARCHAR(200),"
                + "gjyt VARCHAR(200),gy VARCHAR(200),dy VARCHAR(200),weight VARCHAR(200),heart VARCHAR(200),tzqt VARCHAR(200),sfjy VARCHAR(200),sfjj VARCHAR(200),sfbn VARCHAR(200),sm VARCHAR(200),sfwx VARCHAR(200),zyz VARCHAR(200),xltz VARCHAR(200),fyycx VARCHAR(200),ywblfy VARCHAR(200),sffl VARCHAR(200),ywmc1 VARCHAR(200),mr1 VARCHAR(200),mc1 VARCHAR(200),ywmc2 VARCHAR(200),mr2 VARCHAR(200),mc2 VARCHAR(200),ywmc3 VARCHAR(200),mr3 VARCHAR(200),mc3 VARCHAR(200),sfys VARCHAR(200))");

//        db.execSQL("ALTER TABLE lfsgjy ADD COLUMN sysy char(20)");

        //老年慢性支气管炎
        db.execSQL("create table if not exists mxzqgy "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),"
                + "sex VARCHAR(200),happentime VARCHAR(200),visityType VARCHAR(200),sfbr VARCHAR(200),"
                + "tgrgx VARCHAR(200),zz VARCHAR(200),zzqt VARCHAR(200),gy VARCHAR(200),dy VARCHAR(200),"
                + "weight VARCHAR(200),heart VARCHAR(200),hx VARCHAR(200),tzqt VARCHAR(200),sfjy VARCHAR(200),sfjj VARCHAR(200),ydcs VARCHAR(200),ydsc VARCHAR(200),ysqk VARCHAR(200),xltz VARCHAR(200),zyxw VARCHAR(200),fyycx VARCHAR(200),ywblfy VARCHAR(200),ywmc1 VARCHAR(200),mr1 VARCHAR(200),mc1 VARCHAR(200),ywmc2 VARCHAR(200),mr2 VARCHAR(200),mc2 VARCHAR(200),ywmc3 VARCHAR(200),mr3 VARCHAR(200),mc3 VARCHAR(200),sffl VARCHAR(200),sfys VARCHAR(200))");


        //慢阻肺
        db.execSQL("create table if not exists mzf "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),"
                + "sex VARCHAR(200),happentime VARCHAR(200),visityType VARCHAR(200),sfbr VARCHAR(200),"
                + "tgrgx VARCHAR(200),zz VARCHAR(200),zzqt VARCHAR(200),tz VARCHAR(200),tzqt VARCHAR(200),"
                + "sfjy VARCHAR(200),ydcs VARCHAR(200),ydsc VARCHAR(200),hxdl VARCHAR(200),xltz VARCHAR(200),ywmc1 VARCHAR(200),mr1 VARCHAR(200),mc1 VARCHAR(200),ywmc2 VARCHAR(200),mr2 VARCHAR(200),mc2 VARCHAR(200),ywmc3 VARCHAR(200),mr3 VARCHAR(200),mc3 VARCHAR(200),ywmc4 VARCHAR(200),mr4 VARCHAR(200),mc4 VARCHAR(200),ywblfy VARCHAR(200),fyycx VARCHAR(200),sffl VARCHAR(200),sfys VARCHAR(200))");

        //一般人群
        db.execSQL("create table if not exists ybrq "
                + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,personid VARCHAR(200),name VARCHAR(200),"
                + "sex VARCHAR(200),happentime VARCHAR(200),visityType VARCHAR(200),tw VARCHAR(200),"
                + "ml VARCHAR(200),hxpl VARCHAR(200),gy VARCHAR(200),dy VARCHAR(200),height VARCHAR(200),"
                + "weight VARCHAR(200),yaowei VARCHAR(200),bmi VARCHAR(200),ysxg VARCHAR(200),xyqk VARCHAR(200),rxyl VARCHAR(200),yjpl VARCHAR(200),ryjl VARCHAR(200),sfjj VARCHAR(200),jjnl VARCHAR(200),zybjcs VARCHAR(200),gz VARCHAR(200),cysj VARCHAR(200),fc VARCHAR(200),fcfhcs VARCHAR(200),fcfhcsc VARCHAR(200),fswz VARCHAR(200),fswzfhcs VARCHAR(200),fswzfhcsc VARCHAR(200),wlys VARCHAR(200),wlysfhcs VARCHAR(200),wlysfhcsc VARCHAR(200),hxwz VARCHAR(200),hxwzfhcs VARCHAR(200),hxwzfhcsc VARCHAR(200),qtdw VARCHAR(200),qtdwfhcs VARCHAR(200),qtdwfhcsc VARCHAR(200),wxyskz VARCHAR(200),mbtz VARCHAR(200),wxyskzqt VARCHAR(200),sfys VARCHAR(200))");

        Log.e("TAG", "添加数据库了");
    }

    public void addSF(String sql) {
        db.execSQL(sql);
    }

    public JSONArray getGXB(String sql) {
        db.beginTransaction(); // 开始事务
        System.out.println("111");
        JSONArray jsonArray = new JSONArray();
        try {
            System.out.println("222");
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                System.out.println("333");
                JSONObject tmpObj = new JSONObject();

                try {
                    tmpObj.put("personid", cursor.getString(cursor.getColumnIndex("personid")));
                    tmpObj.put("name", cursor.getString(cursor.getColumnIndex("name")));
                    tmpObj.put("sex", cursor.getString(cursor.getColumnIndex("sex")));
                    tmpObj.put("happentime", cursor.getString(cursor.getColumnIndex("happentime")));
                    tmpObj.put("visityType", cursor.getString(cursor.getColumnIndex("visityType")));
                    tmpObj.put("sfbr", cursor.getString(cursor.getColumnIndex("sfbr")));
                    tmpObj.put("tgrgx", cursor.getString(cursor.getColumnIndex("tgrgx")));
                    tmpObj.put("height", cursor.getString(cursor.getColumnIndex("height")));
                    tmpObj.put("weight", cursor.getString(cursor.getColumnIndex("weight")));
                    tmpObj.put("yaowei", cursor.getString(cursor.getColumnIndex("yaowei")));
                    tmpObj.put("ywfb", cursor.getString(cursor.getColumnIndex("ywfb")));
                    tmpObj.put("fbcs", cursor.getString(cursor.getColumnIndex("fbcs")));
                    tmpObj.put("zd", cursor.getString(cursor.getColumnIndex("zd")));
                    tmpObj.put("zdjg", cursor.getString(cursor.getColumnIndex("zdjg")));
                    tmpObj.put("gy", cursor.getString(cursor.getColumnIndex("gy")));
                    tmpObj.put("dy", cursor.getString(cursor.getColumnIndex("dy")));
                    tmpObj.put("kzqk", cursor.getString(cursor.getColumnIndex("kzqk")));
                    tmpObj.put("sfxf", cursor.getString(cursor.getColumnIndex("sfxf")));
                    tmpObj.put("qztime", cursor.getString(cursor.getColumnIndex("qztime")));
                    tmpObj.put("sffy", cursor.getString(cursor.getColumnIndex("sffy")));
                    tmpObj.put("yyqk", cursor.getString(cursor.getColumnIndex("yyqk")));
                    tmpObj.put("sfys", cursor.getString(cursor.getColumnIndex("sfys")));

                    jsonArray.put(tmpObj);
                }catch (Exception e){
                    System.out.println("异常了22："+e.toString());
                }
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }

        return jsonArray;
    }

    public JSONArray getYBRQ(String sql) {
        Log.e("TAG", "111222：" + sql);
        db.beginTransaction(); // 开始事务

        JSONArray jsonArray = new JSONArray();
        JSONObject tmpObj = null;
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                tmpObj = new JSONObject();

                tmpObj.put("personid",cursor.getString(cursor.getColumnIndex("personid")));
                tmpObj.put("name",cursor.getString(cursor.getColumnIndex("name")));
                tmpObj.put("sex",cursor.getString(cursor.getColumnIndex("sex")));
                tmpObj.put("happentime",cursor.getString(cursor.getColumnIndex("happentime")));
                tmpObj.put("visityType",cursor.getString(cursor.getColumnIndex("visityType")));
                tmpObj.put("tw",cursor.getString(cursor.getColumnIndex("tw")));
                tmpObj.put("ml",cursor.getString(cursor.getColumnIndex("ml")));
                tmpObj.put("hxpl",cursor.getString(cursor.getColumnIndex("hxpl")));
                tmpObj.put("gy",cursor.getString(cursor.getColumnIndex("gy")));
                tmpObj.put("dy",cursor.getString(cursor.getColumnIndex("dy")));
                tmpObj.put("height",cursor.getString(cursor.getColumnIndex("height")));
                tmpObj.put("weight",cursor.getString(cursor.getColumnIndex("weight")));
                tmpObj.put("yaowei",cursor.getString(cursor.getColumnIndex("yaowei")));
                tmpObj.put("bmi",cursor.getString(cursor.getColumnIndex("bmi")));
                tmpObj.put("ysxg",cursor.getString(cursor.getColumnIndex("ysxg")));
                tmpObj.put("dy",cursor.getString(cursor.getColumnIndex("dy")));
                tmpObj.put("xyqk",cursor.getString(cursor.getColumnIndex("xyqk")));
                tmpObj.put("rxyl",cursor.getString(cursor.getColumnIndex("rxyl")));
                tmpObj.put("yjpl",cursor.getString(cursor.getColumnIndex("yjpl")));
                tmpObj.put("ryjl",cursor.getString(cursor.getColumnIndex("ryjl")));
                tmpObj.put("sfjj",cursor.getString(cursor.getColumnIndex("sfjj")));
                tmpObj.put("jjnl",cursor.getString(cursor.getColumnIndex("jjnl")));
                tmpObj.put("zybjcs",cursor.getString(cursor.getColumnIndex("zybjcs")));
                tmpObj.put("gz",cursor.getString(cursor.getColumnIndex("gz")));
                tmpObj.put("cysj",cursor.getString(cursor.getColumnIndex("cysj")));
                tmpObj.put("fc",cursor.getString(cursor.getColumnIndex("fc")));
                tmpObj.put("fcfhcs",cursor.getString(cursor.getColumnIndex("fcfhcs")));
                tmpObj.put("fcfhcsc",cursor.getString(cursor.getColumnIndex("fcfhcsc")));
                tmpObj.put("fswz",cursor.getString(cursor.getColumnIndex("fswz")));
                tmpObj.put("fswzfhcs",cursor.getString(cursor.getColumnIndex("fswzfhcs")));
                tmpObj.put("fswzfhcsc",cursor.getString(cursor.getColumnIndex("fswzfhcsc")));
                tmpObj.put("wlys",cursor.getString(cursor.getColumnIndex("wlys")));
                tmpObj.put("wlysfhcs",cursor.getString(cursor.getColumnIndex("wlysfhcs")));
                tmpObj.put("wlysfhcsc",cursor.getString(cursor.getColumnIndex("wlysfhcsc")));
                tmpObj.put("hxwz",cursor.getString(cursor.getColumnIndex("hxwz")));
                tmpObj.put("hxwzfhcs",cursor.getString(cursor.getColumnIndex("hxwzfhcs")));
                tmpObj.put("hxwzfhcsc",cursor.getString(cursor.getColumnIndex("hxwzfhcsc")));
                tmpObj.put("qtdw",cursor.getString(cursor.getColumnIndex("qtdw")));
                tmpObj.put("qtdwfhcs",cursor.getString(cursor.getColumnIndex("qtdwfhcs")));
                tmpObj.put("qtdwfhcsc",cursor.getString(cursor.getColumnIndex("qtdwfhcsc")));
                tmpObj.put("wxyskz",cursor.getString(cursor.getColumnIndex("wxyskz")));
                tmpObj.put("mbtz",cursor.getString(cursor.getColumnIndex("mbtz")));
                tmpObj.put("wxyskzqt",cursor.getString(cursor.getColumnIndex("wxyskzqt")));
                tmpObj.put("sfys",cursor.getString(cursor.getColumnIndex("sfys")));

                jsonArray.put(tmpObj);
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }
        return jsonArray;
    }

    public JSONArray getGJY(String sql) {
        Log.e("TAG", "111222：" + sql);
        db.beginTransaction(); // 开始事务

        JSONArray jsonArray = new JSONArray();
        JSONObject tmpObj = null;
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {

                tmpObj = new JSONObject();

                tmpObj.put("personid",cursor.getString(cursor.getColumnIndex("personid")));
                tmpObj.put("name",cursor.getString(cursor.getColumnIndex("name")));
                tmpObj.put("sex",cursor.getString(cursor.getColumnIndex("sex")));
                tmpObj.put("happentime",cursor.getString(cursor.getColumnIndex("happentime")));
                tmpObj.put("visityType",cursor.getString(cursor.getColumnIndex("visityType")));
                tmpObj.put("sfbr",cursor.getString(cursor.getColumnIndex("sfbr")));
                tmpObj.put("tgrgx",cursor.getString(cursor.getColumnIndex("tgrgx")));
                tmpObj.put("zz",cursor.getString(cursor.getColumnIndex("zz")));
                tmpObj.put("zzqt",cursor.getString(cursor.getColumnIndex("zzqt")));
                tmpObj.put("gjzz",cursor.getString(cursor.getColumnIndex("gjzz")));
                tmpObj.put("gjjx",cursor.getString(cursor.getColumnIndex("gjjx")));
                tmpObj.put("gjyt",cursor.getString(cursor.getColumnIndex("gjyt")));
                tmpObj.put("mcg",cursor.getString(cursor.getColumnIndex("mcg")));
                tmpObj.put("weight",cursor.getString(cursor.getColumnIndex("weight")));
                tmpObj.put("tzqt",cursor.getString(cursor.getColumnIndex("tzqt")));
                tmpObj.put("kztz",cursor.getString(cursor.getColumnIndex("kztz")));
                tmpObj.put("xltz",cursor.getString(cursor.getColumnIndex("xltz")));
                tmpObj.put("xz",cursor.getString(cursor.getColumnIndex("xz")));
                tmpObj.put("qc",cursor.getString(cursor.getColumnIndex("qc")));
                tmpObj.put("yy",cursor.getString(cursor.getColumnIndex("yy")));
                tmpObj.put("fyycx",cursor.getString(cursor.getColumnIndex("fyycx")));
                tmpObj.put("ywblfy",cursor.getString(cursor.getColumnIndex("ywblfy")));

                tmpObj.put("sffl",cursor.getString(cursor.getColumnIndex("sffl")));
                tmpObj.put("ywmc1",cursor.getString(cursor.getColumnIndex("ywmc1")));
                tmpObj.put("mr1",cursor.getString(cursor.getColumnIndex("mr1")));
                tmpObj.put("mc1",cursor.getString(cursor.getColumnIndex("mc1")));
                tmpObj.put("ywmc2",cursor.getString(cursor.getColumnIndex("ywmc2")));
                tmpObj.put("mr2",cursor.getString(cursor.getColumnIndex("mr2")));

                tmpObj.put("mc2",cursor.getString(cursor.getColumnIndex("mc2")));
                tmpObj.put("ywmc3",cursor.getString(cursor.getColumnIndex("ywmc3")));
                tmpObj.put("mr3",cursor.getString(cursor.getColumnIndex("mr3")));
                tmpObj.put("mc3",cursor.getString(cursor.getColumnIndex("mc3")));
                tmpObj.put("sfys",cursor.getString(cursor.getColumnIndex("sfys")));

                jsonArray.put(tmpObj);
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }
        return jsonArray;
    }


    public JSONArray getLFSGJY(String sql) {
        Log.e("TAG", "111222：" + sql);
        db.beginTransaction(); // 开始事务

        JSONArray jsonArray = new JSONArray();
        JSONObject tmpObj = null;
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {

                tmpObj = new JSONObject();

                tmpObj.put("sysy",cursor.getString(cursor.getColumnIndex("sysy")));
                tmpObj.put("personid",cursor.getString(cursor.getColumnIndex("personid")));
                tmpObj.put("name",cursor.getString(cursor.getColumnIndex("name")));
                tmpObj.put("sex",cursor.getString(cursor.getColumnIndex("sex")));
                tmpObj.put("happentime",cursor.getString(cursor.getColumnIndex("happentime")));
                tmpObj.put("visityType",cursor.getString(cursor.getColumnIndex("visityType")));
                tmpObj.put("sfbr",cursor.getString(cursor.getColumnIndex("sfbr")));
                tmpObj.put("tgrgx",cursor.getString(cursor.getColumnIndex("tgrgx")));
                tmpObj.put("zz",cursor.getString(cursor.getColumnIndex("zz")));
                tmpObj.put("zzqt",cursor.getString(cursor.getColumnIndex("zzqt")));
                tmpObj.put("gjjx",cursor.getString(cursor.getColumnIndex("gjjx")));
                tmpObj.put("lfsjj",cursor.getString(cursor.getColumnIndex("lfsjj")));
                tmpObj.put("gjyt",cursor.getString(cursor.getColumnIndex("gjyt")));
                tmpObj.put("gy",cursor.getString(cursor.getColumnIndex("gy")));
                tmpObj.put("dy",cursor.getString(cursor.getColumnIndex("dy")));
                tmpObj.put("weight",cursor.getString(cursor.getColumnIndex("weight")));
                tmpObj.put("heart",cursor.getString(cursor.getColumnIndex("heart")));
                tmpObj.put("tzqt",cursor.getString(cursor.getColumnIndex("tzqt")));
                tmpObj.put("sfjy",cursor.getString(cursor.getColumnIndex("sfjy")));
                tmpObj.put("sfjj",cursor.getString(cursor.getColumnIndex("sfjj")));
                tmpObj.put("sfbn",cursor.getString(cursor.getColumnIndex("sfbn")));
                tmpObj.put("sm",cursor.getString(cursor.getColumnIndex("sm")));
                tmpObj.put("sfwx",cursor.getString(cursor.getColumnIndex("sfwx")));
                tmpObj.put("zyz",cursor.getString(cursor.getColumnIndex("zyz")));

                tmpObj.put("xltz",cursor.getString(cursor.getColumnIndex("xltz")));
                tmpObj.put("fyycx",cursor.getString(cursor.getColumnIndex("fyycx")));
                tmpObj.put("ywblfy",cursor.getString(cursor.getColumnIndex("ywblfy")));
                tmpObj.put("sffl",cursor.getString(cursor.getColumnIndex("sffl")));
                tmpObj.put("ywmc1",cursor.getString(cursor.getColumnIndex("ywmc1")));
                tmpObj.put("mr1",cursor.getString(cursor.getColumnIndex("mr1")));

                tmpObj.put("mc1",cursor.getString(cursor.getColumnIndex("mc1")));
                tmpObj.put("ywmc2",cursor.getString(cursor.getColumnIndex("ywmc2")));
                tmpObj.put("mr2",cursor.getString(cursor.getColumnIndex("mr2")));
                tmpObj.put("mc2",cursor.getString(cursor.getColumnIndex("mc2")));
                tmpObj.put("ywmc3",cursor.getString(cursor.getColumnIndex("ywmc3")));
                tmpObj.put("mr3",cursor.getString(cursor.getColumnIndex("mr3")));
                tmpObj.put("mc3",cursor.getString(cursor.getColumnIndex("mc3")));
                tmpObj.put("sfys",cursor.getString(cursor.getColumnIndex("sfys")));

                jsonArray.put(tmpObj);
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }
        return jsonArray;
    }


    public JSONArray getLNMXZQGY(String sql) {
        Log.e("TAG", "111222：" + sql);
        db.beginTransaction(); // 开始事务

        JSONArray jsonArray = new JSONArray();
        JSONObject tmpObj = null;
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {

                tmpObj = new JSONObject();

                tmpObj.put("personid",cursor.getString(cursor.getColumnIndex("personid")));
                tmpObj.put("name",cursor.getString(cursor.getColumnIndex("name")));
                tmpObj.put("sex",cursor.getString(cursor.getColumnIndex("sex")));
                tmpObj.put("happentime",cursor.getString(cursor.getColumnIndex("happentime")));
                tmpObj.put("visityType",cursor.getString(cursor.getColumnIndex("visityType")));
                tmpObj.put("sfbr",cursor.getString(cursor.getColumnIndex("sfbr")));
                tmpObj.put("tgrgx",cursor.getString(cursor.getColumnIndex("tgrgx")));
                tmpObj.put("zz",cursor.getString(cursor.getColumnIndex("zz")));
                tmpObj.put("zzqt",cursor.getString(cursor.getColumnIndex("zzqt")));
                tmpObj.put("gy",cursor.getString(cursor.getColumnIndex("gy")));
                tmpObj.put("dy",cursor.getString(cursor.getColumnIndex("dy")));
                tmpObj.put("weight",cursor.getString(cursor.getColumnIndex("weight")));
                tmpObj.put("heart",cursor.getString(cursor.getColumnIndex("heart")));
                tmpObj.put("hx",cursor.getString(cursor.getColumnIndex("hx")));
                tmpObj.put("tzqt",cursor.getString(cursor.getColumnIndex("tzqt")));
                tmpObj.put("sfjy",cursor.getString(cursor.getColumnIndex("sfjy")));
                tmpObj.put("sfjj",cursor.getString(cursor.getColumnIndex("sfjj")));
                tmpObj.put("ydcs",cursor.getString(cursor.getColumnIndex("ydcs")));
                tmpObj.put("ydsc",cursor.getString(cursor.getColumnIndex("ydsc")));
                tmpObj.put("ysqk",cursor.getString(cursor.getColumnIndex("ysqk")));
                tmpObj.put("xltz",cursor.getString(cursor.getColumnIndex("xltz")));
                tmpObj.put("zyxw",cursor.getString(cursor.getColumnIndex("zyxw")));
                tmpObj.put("fyycx",cursor.getString(cursor.getColumnIndex("fyycx")));

                tmpObj.put("ywblfy",cursor.getString(cursor.getColumnIndex("ywblfy")));
                tmpObj.put("ywmc1",cursor.getString(cursor.getColumnIndex("ywmc1")));
                tmpObj.put("mr1",cursor.getString(cursor.getColumnIndex("mr1")));
                tmpObj.put("mc1",cursor.getString(cursor.getColumnIndex("mc1")));
                tmpObj.put("ywmc2",cursor.getString(cursor.getColumnIndex("ywmc2")));
                tmpObj.put("mr2",cursor.getString(cursor.getColumnIndex("mr2")));

                tmpObj.put("mc2",cursor.getString(cursor.getColumnIndex("mc2")));
                tmpObj.put("ywmc3",cursor.getString(cursor.getColumnIndex("ywmc3")));
                tmpObj.put("mr3",cursor.getString(cursor.getColumnIndex("mr3")));
                tmpObj.put("mc3",cursor.getString(cursor.getColumnIndex("mc3")));
                tmpObj.put("sffl",cursor.getString(cursor.getColumnIndex("sffl")));
                tmpObj.put("sfys",cursor.getString(cursor.getColumnIndex("sfys")));

                jsonArray.put(tmpObj);
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }
        return jsonArray;
    }


    public JSONArray getMZF(String sql) {
        Log.e("TAG", "111222：" + sql);
        db.beginTransaction(); // 开始事务

        JSONArray jsonArray = new JSONArray();
        JSONObject tmpObj = null;
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {

                tmpObj = new JSONObject();

                tmpObj.put("personid",cursor.getString(cursor.getColumnIndex("personid")));
                tmpObj.put("name",cursor.getString(cursor.getColumnIndex("name")));
                tmpObj.put("sex",cursor.getString(cursor.getColumnIndex("sex")));
                tmpObj.put("happentime",cursor.getString(cursor.getColumnIndex("happentime")));
                tmpObj.put("visityType",cursor.getString(cursor.getColumnIndex("visityType")));
                tmpObj.put("sfbr",cursor.getString(cursor.getColumnIndex("sfbr")));
                tmpObj.put("tgrgx",cursor.getString(cursor.getColumnIndex("tgrgx")));
                tmpObj.put("zz",cursor.getString(cursor.getColumnIndex("zz")));
                tmpObj.put("zzqt",cursor.getString(cursor.getColumnIndex("zzqt")));
                tmpObj.put("tz",cursor.getString(cursor.getColumnIndex("tz")));
                tmpObj.put("tzqt",cursor.getString(cursor.getColumnIndex("tzqt")));
                tmpObj.put("sfjy",cursor.getString(cursor.getColumnIndex("sfjy")));
                tmpObj.put("ydcs",cursor.getString(cursor.getColumnIndex("ydcs")));
                tmpObj.put("ydsc",cursor.getString(cursor.getColumnIndex("ydsc")));
                tmpObj.put("hxdl",cursor.getString(cursor.getColumnIndex("hxdl")));
                tmpObj.put("xltz",cursor.getString(cursor.getColumnIndex("xltz")));
                tmpObj.put("ywmc1",cursor.getString(cursor.getColumnIndex("ywmc1")));
                tmpObj.put("mr1",cursor.getString(cursor.getColumnIndex("mr1")));
                tmpObj.put("mc1",cursor.getString(cursor.getColumnIndex("mc1")));
                tmpObj.put("ywmc2",cursor.getString(cursor.getColumnIndex("ywmc2")));
                tmpObj.put("mr2",cursor.getString(cursor.getColumnIndex("mr2")));
                tmpObj.put("mc2",cursor.getString(cursor.getColumnIndex("mc2")));
                tmpObj.put("ywmc3",cursor.getString(cursor.getColumnIndex("ywmc3")));
                tmpObj.put("mr3",cursor.getString(cursor.getColumnIndex("mr3")));
                tmpObj.put("mc3",cursor.getString(cursor.getColumnIndex("mc3")));
                tmpObj.put("ywmc4",cursor.getString(cursor.getColumnIndex("ywmc4")));
                tmpObj.put("mr4",cursor.getString(cursor.getColumnIndex("mr4")));
                tmpObj.put("mc4",cursor.getString(cursor.getColumnIndex("mc4")));
                tmpObj.put("ywblfy",cursor.getString(cursor.getColumnIndex("ywblfy")));
                tmpObj.put("fyycx",cursor.getString(cursor.getColumnIndex("fyycx")));
                tmpObj.put("sffl",cursor.getString(cursor.getColumnIndex("sffl")));
                tmpObj.put("sfys",cursor.getString(cursor.getColumnIndex("sfys")));

                jsonArray.put(tmpObj);
            }
            cursor.close();
            db.setTransactionSuccessful(); // 设置事务成功完成
            db.endTransaction(); // 结束事务
        } catch (Exception e) {
            Log.e("TAG", "异常了：" + e.toString());
        }
        return jsonArray;
    }
    /**
     * add persons
     */
    public int addPhoto(HD_PERSONAL_HEALTH_PHOTO person) {
        int flag = 0;
        db.beginTransaction(); // 开始事务
        try {
            if (!isExistPhoto()) {
                db.execSQL("CREATE TABLE IF NOT EXISTS HD_PERSONAL_HEALTH_PHOTO "
                        + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,CARDID VARCHAR(20),PHYSICALID VARCHAR(30),"
                        + "PERSONALID VARCHAR(30),PHOTO binary,CREATETIME VARCHAR(30),CREATER VARCHAR(30),"
                        + "CREATEORG VARCHAR(30),RES0 VARCHAR(30),RES1 VARCHAR(30),RES2 VARCHAR(30),RES3 VARCHAR(30),"
                        + "RES4 VARCHAR(30),RES5 VARCHAR(30),RES6 VARCHAR(30),RES7 VARCHAR(30),RES8 VARCHAR(30),RES9 VARCHAR(30))");
            }
            db.execSQL(
                    "insert into HD_PERSONAL_HEALTH_PHOTO(CARDID,PHOTO,CREATETIME,CREATEORG,CREATER)values(?,?,?,?,?)",
                    new Object[]{person.getCARDID(), person.getPHOTO(), person.getCREATETIME(), person.getCREATEORG(),
                            person.getCREATER()});
            db.setTransactionSuccessful(); // 设置事务成功完成
            // Log.w("图片插入数据库----成功-----", "tt5555" );
            flag = 1;
        } catch (Exception e) {
            e.printStackTrace();
            flag = -1;
            Log.w("图片插入数据库----异常-----", "tt" + e.getMessage());
        } finally {
            db.endTransaction(); // 结束事务
        }
        return flag;

    }

    /**
     * 更改照片状态
     */
    public int updatePhoto(HD_PERSONAL_HEALTH_PHOTO person) {
        int flag = 1;
        try {
            db.execSQL("update HD_PERSONAL_HEALTH_PHOTO set PHOTO=?,CREATETIME=?,CREATEORG=?,CREATER=? where CARDID=?",
                    new Object[]{person.getPHOTO(), person.getCREATETIME(), person.getCREATEORG(),
                            person.getCREATER(), person.getCARDID()});
        } catch (Exception e) {
            e.printStackTrace();
            flag = -1;
        }
        return flag;
    }

    /**
     * 查询data
     */
    public List<HD_PERSONAL_HEALTH_PHOTO> selectDataPHOTO(String CARDID) {
        List<HD_PERSONAL_HEALTH_PHOTO> list = new ArrayList<HD_PERSONAL_HEALTH_PHOTO>();
        try {
            String strDataPHOTOSql = "select * from HD_PERSONAL_HEALTH_PHOTO  where  CARDID= '" + CARDID + "'";
            Cursor cursor = db.rawQuery(strDataPHOTOSql, null);
            Log.w("sql---插卡图片", strDataPHOTOSql);
            while (cursor.moveToNext()) {
                HD_PERSONAL_HEALTH_PHOTO ques = new HD_PERSONAL_HEALTH_PHOTO();
                ques.setCARDID(cursor.getString(cursor.getColumnIndex("CARDID")));
                ques.setPHOTO(cursor.getBlob(cursor.getColumnIndex("PHOTO")));
                list.add(ques);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }

    /**
     * 查询当前管理人数
     */
    public int getCountByJDR(String str_jdrno) {
        int count = 0;
        try {
            String strCountByJDRSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "'";
            Cursor cursor = db.rawQuery(strCountByJDRSql, null);
            Log.w("sql---查询当前人数", strCountByJDRSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }

    /**
     * 查询当前管理人数 已上传
     */
    public int getCountByJDRUp(String str_jdrno) {
        int count = 0;
        try {
            String strCountByJDRSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and  IS_UPDATE='N'";
            Cursor cursor = db.rawQuery(strCountByJDRSql, null);
            Log.w("sql---查询当前人数", strCountByJDRSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }

    /**
     * 查询当前管理人数 未上传
     */
    public int getCountByJDRNoUp(String str_jdrno) {
        int count = 0;
        try {
            String strCountByJDRSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and  IS_UPDATE  is not 'N'";
            Cursor cursor = db.rawQuery(strCountByJDRSql, null);
            Log.w("sql---查询当前人数", strCountByJDRSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }

    /**
     * 查询新增人数
     */
    public int getCountByJDRCurrentDate(String str_jdrno, String str_currentDate) {
        int count = 0;
        try {
            String strCountByJDRCurrentDateSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and date(CREATED_DATE)=date('" + str_currentDate + "')";

            Cursor cursor = db.rawQuery(strCountByJDRCurrentDateSql, null);
            Log.w("sql---今日新增人数", strCountByJDRCurrentDateSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }

    /**
     * 查询类别 1:一般人群，2：儿童，3：孕产妇，4：老年人，5：重精神病，6：高血压，7：糖尿病，8：冠心病，9：脑卒中 10 残疾人默认值1
     */
    public int getCountByJDRType(String str_jdrno, String str_type) {
        int count = 0;
        try {
            String strCountByJDRTypeSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and (','||POPULATION_TYPE||',') like '%," + str_type
                    + ",%'";
            Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
            Log.w("sql---查询疾病类型种类" + str_type, strCountByJDRTypeSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;
    }

    public void getCountByJDRTypeTest() {

        int count = 0;
        try {
            String strCountByJDRTypeSql = "select * from ARCHIVE_BASEINFO  where  CUSTOMERNAME='梁济满'";
            Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
            Log.w("sql---查询疾病类型种类", strCountByJDRTypeSql);
            while (cursor.moveToNext()) {
                String aa = cursor.getString(cursor.getColumnIndex("POPULATION_TYPE"));
                Log.e("TAG","患者类别:"+aa);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
    }

    public int getCountByJDRTypeAAA(String str_jdrno, String str_type) {

        int count = 0;
        try {
            String strCountByJDRTypeSql = "select * from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and (','||POPULATION_TYPE||',') like '%," + str_type
                    + ",%'";
            Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
            Log.w("sql---查询疾病类型种类" + str_type, strCountByJDRTypeSql);
            while (cursor.moveToNext()) {
                String aa = cursor.getString(cursor.getColumnIndex("POPULATION_TYPE"));
                Log.e("TAG","Giao:"+aa);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;
    }

    public int getCountByJDRType2(String str_jdrno, String str_type) {
        int count = 0;
        try {
            String strCountByJDRTypeSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and (','||POPULATION_TYPE||',') like '%," + str_type
                    + ",%'";
            Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
            Log.w("sql---查询疾病类型种类" + str_type, strCountByJDRTypeSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;
    }

    /**
     * 查询类别 1:一般人群，2：儿童，3：孕产妇，4：老年人，5：重精神病，6：高血压，7：糖尿病，8：冠心病，9：脑卒中 10 残疾人默认值1
     */
    public int getCountByJDRSEX(String str_jdrno, String sex) {
        int count = 0;
        try {
            String strCountByJDRTypeSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno + "' and SEX='" + sex + "'";
            Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
            Log.w("sql---查询性别" + sex, strCountByJDRTypeSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;
    }

    public List<ARCHIVE_BASEINFO> selectJBPerson(String str_jdrno, int currentpage, String type, String searchContent) {
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;

            String idContent = " AND a.POPULATION_TYPE = '" + searchContent + "'";

            String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                    + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                    + "'  " + idContent + " order by created_date desc limit " + begin + "," + pagesize;

            Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
            Log.w("sql---查找机构--type", str_sqlSignByQYSignPage + "--" + idContent);

            while (cursor.moveToNext()) {
                ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                info.setBIRTHDAY(
                        GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                Date date = format.parse(myDate);
                info.setCREATED_DATE(myDate);
                // 作为签约状态
                info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                list.add(info);
            }
            cursor.close();


        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    public List<ARCHIVE_BASEINFO> selectAllJBPerson(String str_jdrno, int currentpage, String type, String searchContent) {
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;

            int loadNum = currentpage * pagesize;

            String idContent = " AND (','||a.POPULATION_TYPE||',') like '%," + searchContent + ",%'";

            String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                    + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                    + "'  " + idContent + " order by created_date desc";

            Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
            Log.w("sql---查找机构--type", str_sqlSignByQYSignPage + "--" + idContent);

            while (cursor.moveToNext()) {
                ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                info.setBIRTHDAY(
                        GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                Date date = format.parse(myDate);
                info.setCREATED_DATE(myDate);
                // 作为签约状态
                info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                list.add(info);
            }
            cursor.close();


        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    public List<ARCHIVE_BASEINFO> selectAllJBPersonSex(String str_jdrno, int currentpage, String type, String searchContent, String sex) {
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;

            int loadNum = currentpage * pagesize;

            String idContent = " AND (','||a.POPULATION_TYPE||',') like '%," + searchContent + ",%'";

            String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                    + "  where a.CREATER='" + str_jdrno + "' AND a.SEX='" + sex + "' AND a.CREATED_BY='" + str_jdrno
                    + "'  " + idContent + " order by created_date desc";
            if (null == sex || sex.equals("")) {
                str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                        + "'  " + idContent + " order by created_date desc";
            }
            if (null == searchContent || searchContent.equals("")) {
                str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                        + "' AND a.SEX='" + sex + "' order by created_date desc";
            }
            Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
            Log.w("sql---查找机构--type", str_sqlSignByQYSignPage + "--" + idContent);

            while (cursor.moveToNext()) {
                ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                info.setBIRTHDAY(
                        GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                Date date = format.parse(myDate);
                info.setCREATED_DATE(myDate);
                // 作为签约状态
                info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                list.add(info);
            }
            cursor.close();


        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 1:一般人群，2：儿童，3：孕产妇，4：老年人，5：重精神病，6：高血压，7：糖尿病，8：冠心病，9：脑卒中 10残疾人 默认值 1 11：肺结核
     * ；12：建档立卡贫困户，13：计划生育特殊家庭 14：卡外贫困户 15：低保户， 默认值1 年龄分类标准：儿童：<=6;孕妇：孕龄标准(14<
     * <=55);老年人：>=65
     */
    public int getCountByJDRJBType(String str_jdrno) {
        int count = 0;
        try {

            String strJDRJBTypeSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdrno
                    + "' and CREATED_BY='" + str_jdrno
                    + "' and  (POPULATION_TYPE in ('5','6','7','8','9','10','11')  )";

            Cursor cursor = db.rawQuery(strJDRJBTypeSql, null);
            Log.w("sql---类型type", strJDRJBTypeSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }

    /**
     * 查询当前管理人数
     */
    public int getCountByJDR(String str_jdr, String str_jdrno, String searchContent) {

        String str_where = " AND ( CUSTOMERNAME like '%" + searchContent + "%' or PHONE like '%" + searchContent
                + "%' or IDCARD like '%" + searchContent + "' or ZHUYUANHAO ='" + searchContent + "') ";
        int count = 0;
        try {
            String strJDRSql = "select count(ID) from ARCHIVE_BASEINFO  where  CREATER='" + str_jdr
                    + "' and CREATED_BY='" + str_jdrno + "'" + (TextUtils.isEmpty(searchContent) ? "" : str_where);

            Cursor cursor = db.rawQuery(strJDRSql, null);
            Log.w("sql---创建人", strJDRSql);
            cursor.moveToFirst();
            long cout_long = cursor.getLong(0);
            count = (int) cout_long;
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
            count = 0;
        }
        return count;

    }


    /**
     * 查询已签约管理人数
     */
    public int getCountByJDRSign(Context context, String str_jdrno) {
//
        int count = 0;
//        List<JTYSCustomerDoctorCont> datas = FDbManager.getInstance(context).findAllByWhere(JTYSCustomerDoctorCont.class,
//                "CREATER='" + NovaApplication.getJgrUserLoginName() + "'");
//
//
//        for (JTYSCustomerDoctorCont data : datas) {
//            Log.e("JTYSCustomerDoctorCont",data.toString());
//
//
//            //个人
//            if (QYInfo.SIGNTYPE_PERSONAL.equals(data.getSigntype())) {
//
//                count=count+1;
//            } else if (QYInfo.SIGNTYPE_FAMILY.equals(data.getSigntype())) {
        List<ARCHIVE_BASEINFO> baseinfos = NovaApplication.fdbmanger.findAllByWhere(ARCHIVE_BASEINFO.class,
                "CREATER='" + str_jdrno + "' and DISTRICT_ID=1");

//                if (baseinfos != null) {
        count = count + baseinfos.size();

//                }


//            }


//        }


        Log.e("签约人数", count + "");
        return count;

    }

    //查询今日签约人数
    public int getCountByJDRNowSign(Context context, String str_jdrno, String time) {
//
        int count = 0;
//        List<JTYSCustomerDoctorCont> datas = FDbManager.getInstance(context).findAllByWhere(JTYSCustomerDoctorCont.class,
//                "CREATER='" + NovaApplication.getJgrUserLoginName() + "'");
//
//
//        for (JTYSCustomerDoctorCont data : datas) {
//            Log.e("JTYSCustomerDoctorCont",data.toString());
//
//
//            //个人
//            if (QYInfo.SIGNTYPE_PERSONAL.equals(data.getSigntype())) {
//
//                count=count+1;
//            } else if (QYInfo.SIGNTYPE_FAMILY.equals(data.getSigntype())) {
        List<JTYSCustomerDoctorCont> baseinfos = NovaApplication.fdbmanger.findAllByWhere(JTYSCustomerDoctorCont.class,
                "CREATER='" + str_jdrno + "' and qysj='" + time + "'");

//                if (baseinfos != null) {
        count = count + baseinfos.size();

//                }


//            }


//        }


        Log.e("签约人数", count + "");
        return count;

    }

    /**
     * 查询已签约管理人数 已上传
     */
    public int getCountByJDRSignYes(String str_jdrno) {
        int count = 0;
        List<ARCHIVE_BASEINFO> baseinfos = NovaApplication.fdbmanger.findAllByWhere(ARCHIVE_BASEINFO.class,
                "CREATER='" + str_jdrno + "' and DISTRICT_ID=1 and IS_UPDATE='N'");


        count = count + baseinfos.size();
        return count;
    }

    /**
     * 查询已签约管理人数 已上传
     */
    public int getCountByJDRSignNo(String str_jdrno) {
        int count = 0;
        List<ARCHIVE_BASEINFO> baseinfos = NovaApplication.fdbmanger.findAllByWhere(ARCHIVE_BASEINFO.class,
                "CREATER='" + str_jdrno + "' and DISTRICT_ID=1 and IS_UPDATE is not 'N'");


        count = count + baseinfos.size();
        return count;
    }

    /**
     * 查询未签约管理人数
     */
    public int getCountByJDRUnSign(String str_jdr, String str_jdrno, String searchContent) {
//
        int count = 0;
        List<ARCHIVE_BASEINFO> baseinfos = NovaApplication.fdbmanger.findAllByWhere(ARCHIVE_BASEINFO.class,
                "CREATER='" + str_jdrno + "' and DISTRICT_ID is null");
        if (baseinfos != null) {
            count = baseinfos.size();
        }
        return count;

    }

    public List<LvYueFuWuBean> getLvYueList(int currentpage, String searchContent) {
        List<LvYueFuWuBean> list = new ArrayList<LvYueFuWuBean>();
        int begin = (currentpage - 1) * pagesize;
        String str_sqlSignByQYSignPage = " select a.id,a.cardNum,a.name,a.age,a.lvyueDate,a.hight,a.weight,a.tzzs,a.huxi,a.xinlv,a.tiWen,a.xueyaGao,a.xueyaDi,a.xueTang,a.jkgl,a.zyqk,a.zzqk,a.docName,a.docQianMingStr,a.huanQianMingStr,a.beiZhu,a.beiZhu1,a.beiZhu2,a.beiZhu3,a.xueTangType,a.kouchun,a.kouchun_qita,a.kouchun_beizhu,a.chilie,a.chilie_qita,a.chilie_beizhu,a.yanbu,a.yanbu_qita,a.yanbu_beizhu,a.tingli,a.tingli_qita,a.tingli_beizhu,a.pifu,a.pifu_qita,a.pifu_beizhu,a.fei_xinlv,a.fei_xinlv_qita,a.fei_xinlv_beizhu,a.zayin,a.zayin_qita,a.zayin_beizhu,a.yatong,a.yatong_qita,a.yatong_beizhu,a.baokuai,a.baokuai_qita,a.baokuai_beizhu,a.ganda,a.ganda_qita,a.ganda_beizhu,a.pida,a.pida_qita,a.pida_beizhu,a.zhuoyin,a.zhuoyin_qita,a.zhuoyin_beizhu,a.jiBing,a.jibing_beizhu,a.jkgy_beizhu,a.zyqk_beizhu,a.yongyao,a.yongyao_beizhu,a.zzqk_beizhu  from LvYueFuWuBean a    "
                + "  where a.cardNum='" + searchContent + "' order by lvyueDate desc limit " + begin + "," + pagesize;

        Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);

        while (cursor.moveToNext()) {
            LvYueFuWuBean info = new LvYueFuWuBean();
            info.setId(cursor.getString(cursor.getColumnIndex("id")));
            info.setCardNum(cursor.getString(cursor.getColumnIndex("cardNum")));
            info.setName(cursor.getString(cursor.getColumnIndex("name")));
            info.setAge(cursor.getString(cursor.getColumnIndex("age")));
            info.setLvyueDate(cursor.getString(cursor.getColumnIndex("lvyueDate")));
            info.setHight(cursor.getString(cursor.getColumnIndex("hight")));
            info.setWeight(cursor.getString(cursor.getColumnIndex("weight")));
            info.setTzzs(cursor.getString(cursor.getColumnIndex("tzzs")));
            info.setHuxi(cursor.getString(cursor.getColumnIndex("huxi")));
            info.setXinlv(cursor.getString(cursor.getColumnIndex("xinlv")));
            info.setTiWen(cursor.getString(cursor.getColumnIndex("tiWen")));
            info.setXueyaGao(cursor.getString(cursor.getColumnIndex("xueyaGao")));
            info.setXueyaDi(cursor.getString(cursor.getColumnIndex("xueyaDi")));
            info.setXueTang(cursor.getString(cursor.getColumnIndex("xueTang")));
            info.setJkgl(cursor.getString(cursor.getColumnIndex("jkgl")));
            info.setZyqk(cursor.getString(cursor.getColumnIndex("zyqk")));
            info.setZzqk(cursor.getString(cursor.getColumnIndex("zzqk")));
            info.setDocName(cursor.getString(cursor.getColumnIndex("docName")));
            info.setDocQianMingStr(cursor.getString(cursor.getColumnIndex("docQianMingStr")));
            info.setHuanQianMingStr(cursor.getString(cursor.getColumnIndex("huanQianMingStr")));
            info.setBeiZhu(cursor.getString(cursor.getColumnIndex("beiZhu")));
            info.setBeiZhu1(cursor.getString(cursor.getColumnIndex("beiZhu1")));
            info.setBeiZhu2(cursor.getString(cursor.getColumnIndex("beiZhu2")));
            info.setBeiZhu3(cursor.getString(cursor.getColumnIndex("beiZhu3")));

            info.setXueTangType(cursor.getString(cursor.getColumnIndex("xueTangType")));
            info.setKouchun(cursor.getString(cursor.getColumnIndex("kouchun")));
            info.setKouchun_qita(cursor.getString(cursor.getColumnIndex("kouchun_qita")));
            info.setKouchun_beizhu(cursor.getString(cursor.getColumnIndex("kouchun_beizhu")));
            info.setChilie(cursor.getString(cursor.getColumnIndex("chilie")));
            info.setChilie_qita(cursor.getString(cursor.getColumnIndex("chilie_qita")));
            info.setChilie_beizhu(cursor.getString(cursor.getColumnIndex("chilie_beizhu")));
            info.setYanbu(cursor.getString(cursor.getColumnIndex("yanbu")));
            info.setYanbu_qita(cursor.getString(cursor.getColumnIndex("yanbu_qita")));
            info.setYanbu_beizhu(cursor.getString(cursor.getColumnIndex("yanbu_beizhu")));
            info.setTingli(cursor.getString(cursor.getColumnIndex("tingli")));
            info.setTingli_qita(cursor.getString(cursor.getColumnIndex("tingli_qita")));
            info.setTingli_beizhu(cursor.getString(cursor.getColumnIndex("tingli_beizhu")));
            info.setPifu(cursor.getString(cursor.getColumnIndex("pifu")));
            info.setPifu_qita(cursor.getString(cursor.getColumnIndex("pifu_qita")));
            info.setPifu_beizhu(cursor.getString(cursor.getColumnIndex("pifu_beizhu")));
            info.setFei_xinlv(cursor.getString(cursor.getColumnIndex("fei_xinlv")));
            info.setFei_xinlv_qita(cursor.getString(cursor.getColumnIndex("fei_xinlv_qita")));
            info.setFei_xinlv_beizhu(cursor.getString(cursor.getColumnIndex("fei_xinlv_beizhu")));
            info.setZayin(cursor.getString(cursor.getColumnIndex("zayin")));
            info.setZayin_qita(cursor.getString(cursor.getColumnIndex("zayin_qita")));
            info.setZayin_beizhu(cursor.getString(cursor.getColumnIndex("zayin_beizhu")));
            info.setYatong(cursor.getString(cursor.getColumnIndex("yatong")));
            info.setYatong_qita(cursor.getString(cursor.getColumnIndex("yatong_qita")));
            info.setYatong_beizhu(cursor.getString(cursor.getColumnIndex("yatong_beizhu")));
            info.setBaokuai(cursor.getString(cursor.getColumnIndex("baokuai")));
            info.setBaokuai_qita(cursor.getString(cursor.getColumnIndex("baokuai_qita")));
            info.setBaokuai_beizhu(cursor.getString(cursor.getColumnIndex("baokuai_beizhu")));
            info.setGanda(cursor.getString(cursor.getColumnIndex("ganda")));
            info.setGanda_qita(cursor.getString(cursor.getColumnIndex("ganda_qita")));
            info.setGanda_beizhu(cursor.getString(cursor.getColumnIndex("ganda_beizhu")));
            info.setPida(cursor.getString(cursor.getColumnIndex("pida")));
            info.setPida_qita(cursor.getString(cursor.getColumnIndex("pida_qita")));
            info.setPida_beizhu(cursor.getString(cursor.getColumnIndex("pida_beizhu")));
            info.setZhuoyin(cursor.getString(cursor.getColumnIndex("zhuoyin")));
            info.setZhuoyin_qita(cursor.getString(cursor.getColumnIndex("zhuoyin_qita")));
            info.setZhuoyin_beizhu(cursor.getString(cursor.getColumnIndex("zhuoyin_beizhu")));
            info.setJiBing(cursor.getString(cursor.getColumnIndex("jiBing")));
            info.setJibing_beizhu(cursor.getString(cursor.getColumnIndex("jibing_beizhu")));
            info.setJkgy_beizhu(cursor.getString(cursor.getColumnIndex("jkgy_beizhu")));
            info.setZyqk_beizhu(cursor.getString(cursor.getColumnIndex("zyqk_beizhu")));
            info.setYongyao(cursor.getString(cursor.getColumnIndex("yongyao")));
            info.setYongyao_beizhu(cursor.getString(cursor.getColumnIndex("yongyao_beizhu")));
            info.setZzqk_beizhu(cursor.getString(cursor.getColumnIndex("zzqk_beizhu")));


            list.add(info);
        }
        cursor.close();
        return list;

    }

    /**
     * 获取用户状态
     *
     * @return
     * @author 王婷婷
     * @date 2017年6月7日 上午10:41:36
     */
    public List<ARCHIVE_BASEINFO> selectUserByQYSignPage(String str_jdrno, int currentpage, String type,
                                                         String searchContent) {
        String str_where = "  CUSTOMERNAME like '%" + searchContent + "%' or PHONE like '%" + searchContent
                + "%' or IDCARD like '%" + searchContent + "' or ZHUYUANHAO ='" + searchContent + "'";
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;
            // 已签约sql
            // String str_sqlSign = "select * from JTYS_KHQYB a left join
            // ARCHIVE_BASEINFO b on b.IDCARD=a.cardNum where b.CREATER='"
            // + str_jdrno + "' AND b.CREATED_BY='" + str_jdrno + "' order by
            // created_date desc limit " + begin
            // + "," + pagesize;

            // 未签约sql
            // String str_sqlUnSign = "select * from ARCHIVE_BASEINFO a where
            // a.CREATER='" + str_jdrno
            // + "' AND a.CREATED_BY='" + str_jdrno
            // + "' and not EXISTS (select cardNum from JTYS_KHQYB where
            // a.IDCARD= cardNum) order by created_date desc limit "
            // + begin + "," + pagesize;
            if (TextUtils.isEmpty(type)) {
                list = new ArrayList<ARCHIVE_BASEINFO>();
            } else {
                String idContent = "";

                if (TYPE_SIGN.equals(type)) {
//                    idContent = " where  id is not null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and  DISTRICT_ID is not null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_UNSIGN.equals(type)) {
//                    idContent = " where id is null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and DISTRICT_ID is null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_ALL.equals(type)) {
//                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " where " + str_where);
                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " and " + str_where);

                }

//                String str_sqlSignByQYSignPage = "select CUSTOMERNAME,BIRTHDAY,SEX,PHONE,POPULATION_TYPE,HOUSEHOLD_ADDR,IDCARD,created_date,id,IS_UPDATE  from (select a.idcard,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,b.id from archive_baseinfo a  left OUTER   join JTYS_KHQYB b "
//                        + " on a.idcard=b.cardnum  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
//                        + "') " + idContent + " order by created_date desc limit " + begin + "," + pagesize;

                String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                        + "'  " + idContent + " order by created_date desc";

                Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
                Log.w("sql---查找机构--type" + "\n" + idContent, str_sqlSignByQYSignPage);

                while (cursor.moveToNext()) {
                    ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                    info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                    info.setBIRTHDAY(
                            GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                    info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                    info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                    info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                    info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                    info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                    info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                    String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                    Date date = format.parse();
                    info.setCREATED_DATE(myDate);
                    // 作为签约状态
                    info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                    info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                    list.add(info);
                }
                cursor.close();
            }

        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    public List<ARCHIVE_BASEINFO> selectUserByQYSignPage2(String str_jdrno, int currentpage, String type,
                                                          String searchContent) {
        String str_where = "  CUSTOMERNAME like '%" + searchContent + "%' or PHONE like '%" + searchContent
                + "%' or IDCARD like '%" + searchContent + "' or ZHUYUANHAO ='" + searchContent + "'";
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;
            // 已签约sql
            // String str_sqlSign = "select * from JTYS_KHQYB a left join
            // ARCHIVE_BASEINFO b on b.IDCARD=a.cardNum where b.CREATER='"
            // + str_jdrno + "' AND b.CREATED_BY='" + str_jdrno + "' order by
            // created_date desc limit " + begin
            // + "," + pagesize;

            // 未签约sql
            // String str_sqlUnSign = "select * from ARCHIVE_BASEINFO a where
            // a.CREATER='" + str_jdrno
            // + "' AND a.CREATED_BY='" + str_jdrno
            // + "' and not EXISTS (select cardNum from JTYS_KHQYB where
            // a.IDCARD= cardNum) order by created_date desc limit "
            // + begin + "," + pagesize;
            if (TextUtils.isEmpty(type)) {
                list = new ArrayList<ARCHIVE_BASEINFO>();
            } else {
                String idContent = "";

                if (TYPE_SIGN.equals(type)) {
//                    idContent = " where  id is not null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and  DISTRICT_ID is not null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_UNSIGN.equals(type)) {
//                    idContent = " where id is null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and DISTRICT_ID is null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_ALL.equals(type)) {
//                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " where " + str_where);
                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " and " + str_where);

                }

//                String str_sqlSignByQYSignPage = "select CUSTOMERNAME,BIRTHDAY,SEX,PHONE,POPULATION_TYPE,HOUSEHOLD_ADDR,IDCARD,created_date,id,IS_UPDATE  from (select a.idcard,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,b.id from archive_baseinfo a  left OUTER   join JTYS_KHQYB b "
//                        + " on a.idcard=b.cardnum  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
//                        + "') " + idContent + " order by created_date desc limit " + begin + "," + pagesize;

                String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                        + "'  " + idContent + " order by created_date desc";

                Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
                Log.w("sql---查找机构--type" + "\n" + idContent, str_sqlSignByQYSignPage);

                while (cursor.moveToNext()) {
                    ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                    info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                    info.setBIRTHDAY(
                            GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                    info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                    info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                    info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                    info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                    info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                    info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                    String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                    Date date = format.parse(myDate);
                    info.setCREATED_DATE(myDate);
                    // 作为签约状态
                    info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                    info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                    list.add(info);
                }
                cursor.close();
            }

        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取用户状态
     *
     * @return
     * @author 王婷婷
     * @date 2017年6月7日 上午10:41:36
     */
    public List<ARCHIVE_BASEINFO> selectUserByQYLoadPage(String str_jdrno, int currentpage, String type, String searchContent) {
        String str_where = "  CUSTOMERNAME like '%" + searchContent + "%' or PHONE like '%" + searchContent
                + "%' or IDCARD like '%" + searchContent + "' or ZHUYUANHAO ='" + searchContent + "'";
        List<ARCHIVE_BASEINFO> list = new ArrayList<ARCHIVE_BASEINFO>();
        try {
            int begin = (currentpage - 1) * pagesize;
            int loadNum = currentpage * pagesize;
            // 已签约sql
            // String str_sqlSign = "select * from JTYS_KHQYB a left join
            // ARCHIVE_BASEINFO b on b.IDCARD=a.cardNum where b.CREATER='"
            // + str_jdrno + "' AND b.CREATED_BY='" + str_jdrno + "' order by
            // created_date desc limit " + begin
            // + "," + pagesize;

            // 未签约sql
            // String str_sqlUnSign = "select * from ARCHIVE_BASEINFO a where
            // a.CREATER='" + str_jdrno
            // + "' AND a.CREATED_BY='" + str_jdrno
            // + "' and not EXISTS (select cardNum from JTYS_KHQYB where
            // a.IDCARD= cardNum) order by created_date desc limit "
            // + begin + "," + pagesize;
            if (TextUtils.isEmpty(type)) {
                list = new ArrayList<ARCHIVE_BASEINFO>();
            } else {
                String idContent = "";

                if (TYPE_SIGN.equals(type)) {
//                    idContent = " where  id is not null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and  DISTRICT_ID is not null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_UNSIGN.equals(type)) {
//                    idContent = " where id is null "
//                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");
                    idContent = " and DISTRICT_ID is null "
                            + (TextUtils.isEmpty(searchContent) ? "" : " and (" + str_where + ")");

                } else if (TYPE_ALL.equals(type)) {
//                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " where " + str_where);
                    idContent = "" + (TextUtils.isEmpty(searchContent) ? "" : " and " + str_where);

                }

//                String str_sqlSignByQYSignPage = "select CUSTOMERNAME,BIRTHDAY,SEX,PHONE,POPULATION_TYPE,HOUSEHOLD_ADDR,IDCARD,created_date,id,IS_UPDATE  from (select a.idcard,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,b.id from archive_baseinfo a  left OUTER   join JTYS_KHQYB b "
//                        + " on a.idcard=b.cardnum  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
//                        + "') " + idContent + " order by created_date desc limit " + begin + "," + pagesize;

//                String str_sqlSignByQYSignPage = " select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE  from archive_baseinfo a    "
//                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
//                        + "'  " + idContent + " order by created_date desc limit " + begin + "," + pagesize;

                String str_sqlSignByQYSignPage = "select a.IDCARD,a.CREATER,a.CUSTOMERNAME,a.BIRTHDAY,a.SEX,a.PHONE,a.POPULATION_TYPE,a.HOUSEHOLD_ADDR,a.created_date,a.IS_UPDATE,a.DISTRICT_ID,a.ZHUYUANHAO,a.CREATED_DATE from archive_baseinfo a    "
                        + "  where a.CREATER='" + str_jdrno + "' AND a.CREATED_BY='" + str_jdrno
                        + "'  " + idContent + " order by created_date desc limit " + loadNum;
                Cursor cursor = db.rawQuery(str_sqlSignByQYSignPage, null);
                Log.w("sql---查找机构--type" + "\n", str_sqlSignByQYSignPage);

                while (cursor.moveToNext()) {
                    ARCHIVE_BASEINFO info = new ARCHIVE_BASEINFO();
                    info.setCUSTOMERNAME(cursor.getString(cursor.getColumnIndex("CUSTOMERNAME")));
                    info.setBIRTHDAY(
                            GlobalConts.datetimeFormat.parse(cursor.getString(cursor.getColumnIndex("BIRTHDAY"))));
                    info.setSEX(cursor.getString(cursor.getColumnIndex("SEX")));
                    info.setPHONE(cursor.getString(cursor.getColumnIndex("PHONE")));
                    info.setPOPULATION_TYPE(cursor.getString(cursor.getColumnIndex("POPULATION_TYPE")));
                    info.setHOUSEHOLD_ADDR(cursor.getString(cursor.getColumnIndex("HOUSEHOLD_ADDR")));
                    info.setIDCARD(cursor.getString(cursor.getColumnIndex("IDCARD")));
                    info.setZHUYUANHAO(cursor.getString(cursor.getColumnIndex("ZHUYUANHAO")));
                    String myDate = cursor.getString(cursor.getColumnIndex("CREATED_DATE"));
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//                    Date date = format.parse(myDate);
                    info.setCREATED_DATE(myDate);
                    // 作为签约状态
                    info.setDISTRICT_ID(cursor.getString(cursor.getColumnIndex("DISTRICT_ID")));
                    info.setIS_UPDATE(cursor.getString(cursor.getColumnIndex("IS_UPDATE")));

                    list.add(info);
                }
                cursor.close();
            }

        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 执行删除操作
     *
     * @param table
     * @param whereClause
     * @param whereArgs
     * @author 王婷婷
     * @date 2017年12月18日 下午3:53:39 String args[] ={ today, content,
     * Boolean.toString(checked)}; dbServices.delete("_today_plan",
     * "[_Date]=? and [Item]=? and [Check]=?",args);
     */
    public void delete(String table, String whereClause, String[] whereArgs) {
        db.delete(table, whereClause, whereArgs);
        Log.d("Delete", whereClause);
    }

    /**
     * 执行删除操作 String sql = "delete from " + bm + " where cardNum='" + cardNum +
     * "';";
     *
     * @author 王婷婷
     * @date 2017年12月18日 下午3:53:39
     */
    public void deleteBySQL(String sqlDeleteData) {
        try {
            Log.e("deleteBySQL", sqlDeleteData);
            db.execSQL(sqlDeleteData);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param tableName  表名
     * @param columnName 列名
     * @return
     */
    public boolean checkColumnExists(String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;

        try {
            cursor = db.rawQuery("select * from sqlite_master where name = ? and sql like ?",
                    new String[]{tableName, "%" + columnName + "%"});
            result = null != cursor && cursor.moveToFirst();
        } catch (Exception e) {
            Log.e("tag", "checkColumnExists2..." + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return result;
    }

    /**
     * 添加列
     */
    public void addColumnByBySQL(String tableName, String columnName) {
        try {
            boolean checkColumnExists = NovaApplication.dbManger.checkColumnExists(tableName, columnName);
            if (!checkColumnExists) {
                String addColumnSQL = "alter table " + tableName + " add column " + columnName + " varchar";
                db.execSQL(addColumnSQL);
                Log.d("addColumnByBySQL", addColumnSQL);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addColumnByBySQLInt(String tableName, String columnName) {
        try {
            boolean checkColumnExists = NovaApplication.dbManger.checkColumnExists(tableName, columnName);
            if (!checkColumnExists) {
                String addColumnSQL = "alter table " + tableName + " add column " + columnName + " int";
                db.execSQL(addColumnSQL);
                Log.d("addColumnByBySQL", addColumnSQL);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加索引
     */
    public void addIndexOnTable() {
        try {
            List<String> sqlStrs = new ArrayList<>();

            // 公卫 ARCHIVE_BASEINFO 添加索引IDCARD，(CREATER,
            // CREATED_BY,POPULATION_TYPE)
            sqlStrs.add(addIndexBySQL("index_baseinfo_idcard", "ARCHIVE_BASEINFO", "IDCARD"));
            sqlStrs.add(
                    addIndexBySQL("index_baseinfo_creater", "ARCHIVE_BASEINFO", "CREATER, CREATED_BY,POPULATION_TYPE"));
            // 家庭医生 JTYS_KHQYB 添加索引cardNum
            sqlStrs.add(addIndexBySQL("index_khqyb_creater", "JTYS_KHQYB", "cardNum"));
            // 家庭医生 JTYS_FWMYD 添加索引(creater,satisfiedState)
            sqlStrs.add(addIndexBySQL("index_fwmyd_creater", "JTYS_FWMYD", "creater,satisfiedState"));

            for (int i = 0, size = sqlStrs.size(); i < size; i++) {
                db.execSQL(sqlStrs.get(i));
                Log.e("addIndexOnTable", sqlStrs.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过sql创建索引
     *
     * @param tableName
     * @author 王婷婷
     * @date 2018年1月12日 下午3:51:01
     */
    private String addIndexBySQL(String indexName, String tableName, String columnNameStr) {
        String sqlStr = "";
        sqlStr = " create index if not exists " + indexName + " on " + tableName + "(" + columnNameStr + ")";
        return sqlStr;
    }

    /**
     * 判断表是否存在
     * @return
     */
    public boolean isExistTable(String tableName) {
        String sql = "select count(*) from sqlite_master where type ='table' and name = '" + tableName + "';";
        try {
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    Log.e(TAG, tableName + ": 数据表已经存在" );
                    return true;
                } else {
                    return false;
                }
            }
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 创建新的体检数据表 对新平台使用
     * @param
     */
    public void createSBCL_DATA_NEWTable() {
        if (!isExistTable("SBCL_DATA_NEW")) {
            db.execSQL("CREATE TABLE SBCL_DATA_NEW ( ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                    " DEVICE_NAME, CL_TYPE, GUID, CL_TYPE_NAME, IS_UPDATE, CL_VALUE," +
                    " CL_TIME, IDCARD, DEVICE_TYPE, IS_UPDATE_JKXZ varchar );");
        }
    }

}
